﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

[Serializable]
public class SaveDataClass
{
    public CommandType cmdType;         //语句的类型：SQL语句or存储过程语句
    public string cmdText;              //执行的SQL语句or存储过程语句
    public SqlParameter[] cmdParms;     //SQL语句or存储过程语句带的参数 
}

/// <summary>
///OpDB 的摘要说明
/// </summary>
public class OpDB
{
    /// <summary>
    ///  无事务取数据表
    /// </summary>
    /// <param name="stCnn">数据库连接</param>
    /// <param name="stSQL">执行的SQL语句</param> 
    /// <returns>返回DataTable，失败返回null</returns>
    public static DataTable GetDataTable(string stCnn, string stSQL)
    {
        DataTable DT = null;//返回的DataTable         
        SqlConnection sqlConn = new SqlConnection(stCnn);
        sqlConn.Open(); //打开链接
        SqlCommand sqlCommand = new SqlCommand();
        try
        {
            DataSet DS = SQLServerHelper.ExecuteDataSet(sqlConn, sqlCommand, null, CommandType.Text, stSQL, null);
            if (DS != null)
            {
                DT = DS.Tables[0];
            }
            return DT;
        }
        catch
        {
            return null;
        }
        finally
        {
            SQLServerHelper.closeConnection(sqlConn);//关闭链接
        }
    }


    /// <summary>
    ///  无事务取数据表
    /// </summary>
    /// <param name="stCnn">数据库连接</param>
    /// <param name="stSQL">执行的存储过程名</param> 
    /// <param name="cmdParms">参数列表</param> 
    /// <returns>返回DataTable，失败返回null</returns>
    public static DataTable GetDataTableBySp(string stCnn, string stSpName, SqlParameter[] cmdParms)
    {
        DataTable DT = null;//返回的DataTable         
        SqlConnection sqlConn = new SqlConnection(stCnn);
        sqlConn.Open(); //打开链接
        SqlCommand sqlCommand = new SqlCommand();
        try
        {
            DataSet DS = SQLServerHelper.ExecuteDataSet(sqlConn, sqlCommand, null, CommandType.StoredProcedure, stSpName, cmdParms);
            if (DS != null)
            {
                DT = DS.Tables[0];
            }
            return DT;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (sqlConn != null)
                SQLServerHelper.closeConnection(sqlConn);//关闭链接
        }
    }

    /// <summary>
    ///  无事务取统计值
    /// </summary>
    /// <param name="stCnn">数据库连接</param>
    /// <param name="stSQL">执行的SQL语句</param> 
    /// <returns>返回一个object类型的数据</returns>
    public static object GetObject(string stCnn, string stSQL)
    {
        SqlConnection sqlConn = new SqlConnection(stCnn);
        sqlConn.Open(); //打开链接
        SqlCommand sqlCommand = new SqlCommand();
        try
        {
            object oReturn = SQLServerHelper.ExecuteScalar(sqlConn, sqlCommand, null, stSQL, null);
            return oReturn;
        }
        catch
        {
            return null;
        }
        finally
        {
            SQLServerHelper.closeConnection(sqlConn);//关闭链接
        }
    }

    /// <summary>
    ///  保存数据（起事务）
    /// </summary>
    /// <param name="stCnn">数据库连接</param>
    /// <param name="stSQL">要执行的SQL语句数组</param> 
    public static bool SaveDataToDB(string stCnn, List<string> stSQL)
    {
        SqlConnection sqlConn = new SqlConnection(stCnn);
        sqlConn.Open(); //打开链接
        SqlTransaction thisTrans = sqlConn.BeginTransaction();//起事务
        SqlCommand sqlCommand = new SqlCommand();
        try
        {
            for (int i = 0; i < stSQL.Count; i++)
            {
                if (SQLServerHelper.ExecuteNonQuery(sqlConn, sqlCommand, thisTrans, CommandType.Text, stSQL[i], null) == -1)
                {
                    thisTrans.Rollback();
                    return false;
                }
            }
            thisTrans.Commit();
            return true;
        }
        catch
        {
            thisTrans.Rollback();
            return false;
        }
        finally
        {
            SQLServerHelper.closeConnection(sqlConn);//关闭链接
        }
    }

    /// <summary>
    ///  保存数据（起事务）
    /// </summary>
    /// <param name="stCnn">数据库连接</param>
    /// <param name="sdcSQL">要执行的SQL语句数组(带执行类型)</param> 
    public static bool SaveDataToDB(string stCnn, List<SaveDataClass> sdcSQL)
    {
        SqlConnection sqlConn = new SqlConnection(stCnn);
        sqlConn.Open(); //打开链接
        SqlTransaction thisTrans = sqlConn.BeginTransaction();//起事务
        SqlCommand sqlCommand = new SqlCommand();
        try
        {
            for (int i = 0; i < sdcSQL.Count; i++)
            {
                if (SQLServerHelper.ExecuteNonQuery(sqlConn, sqlCommand, thisTrans, sdcSQL[i].cmdType, sdcSQL[i].cmdText, sdcSQL[i].cmdParms) == -1)
                {
                    thisTrans.Rollback();
                    return false;
                }
            }
            thisTrans.Commit();
            return true;
        }
        catch
        {
            thisTrans.Rollback();
            return false;
        }
        finally
        {
            SQLServerHelper.closeConnection(sqlConn);//关闭链接
        }
    }
}
